/****** Object:  StoredProcedure [dbo].[SaveArraPIQuestionnaire]    Script Date: 09/21/2009 10:36:02 ******/
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[SaveArraPIQuestionnaire]')
                    AND type IN ( N'P', N'PC' ) ) 
    DROP PROCEDURE [dbo].[SaveArraPIQuestionnaire]
GO

/****** Object:  StoredProcedure [dbo].[SaveArraPIQuestionnaire]    Script Date: 09/21/2009 10:36:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		eivaskevicius
-- Create date: 9/18/2009
-- Description:	Saves PI questionnaire and belonging jobs to DB
-- =============================================
CREATE PROCEDURE [dbo].[SaveArraPIQuestionnaire]
    @arraAgreementID INT,
    @arraPIQuestionnaireID INT,
    @narrative VARCHAR(4000),
    @quarterlyActivities VARCHAR(2000),
    @personID INT,
    @xmlJobs XML,
    @state INT
AS 
    BEGIN
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
        SET NOCOUNT ON ;

        DECLARE @xmlDocHandle INT ;
        DECLARE @err INT ;
        DECLARE @jobs AS TABLE
            (
              ID INT,
              TypeID INT,
              Title VARCHAR(40),
              PercentEffort DECIMAL
            ) ;

        EXEC sp_xml_preparedocument @xmlDocHandle OUTPUT, @xmlJobs ;

		-- check for error
        SET @err = @@ERROR ;
        IF ( @err != 0 ) 
            RETURN @err ;

        INSERT  INTO @jobs
                SELECT  ID,
                        TypeID,
                        Title,
                        PercentEffort
                FROM    OPENXML(@xmlDocHandle, '/Jobs/Job', 2) WITH ( ID INT, TypeID INT, Title VARCHAR(40), PercentEffort DECIMAL ) ;

		-- check for error
        SET @err = @@ERROR ;
        IF ( @err != 0 ) 
            RETURN @err ;

		-- Update PI questionnaire record
        UPDATE  dbo.ArraPIQuestionnaire
        SET     Narrative = @narrative,
                QuarterlyActivities = @quarterlyActivities
        WHERE   dbo.ArraPIQuestionnaire.ID = @arraPIQuestionnaireID ;

		-- check for error
        SET @err = @@ERROR ;
        IF ( @err != 0 ) 
            RETURN @err ;

		-- add an event to the history			
        INSERT  INTO dbo.ArraPIQuestionnaireEventHistory
                (
                  ArraPIQuestionnaireID,
                  EventDate,
                  ArraPIQuestionnaireEventID,
                  CreatedByAdmPersonID
                )
        VALUES  (
                  @arraPIQuestionnaireID,
                  GETDATE(),
                  @state,
                  @personID
                ) ;

		-- check for error
        SET @err = @@ERROR ;
        IF ( @err != 0 ) 
            RETURN @err ;

		-- delete jobs belonging to the questionnaire and insert new ones
        DELETE  FROM dbo.ArraPIQuestionnaireJob
        WHERE   ArraPIQuestionnaireID = @arraPIQuestionnaireID ;

		-- check for error
        SET @err = @@ERROR ;
        IF ( @err != 0 ) 
            RETURN @err ;

        INSERT  INTO dbo.ArraPIQuestionnaireJob
                SELECT  @arraPIQuestionnaireID,
                        TypeID,
                        Title,
                        PercentEffort
                FROM    @jobs ;

		-- check for error
        SET @err = @@ERROR ;
        IF ( @err != 0 ) 
            RETURN @err ;

    END

GO


